package com.seeyon.apps.kk.manager;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.CollectionUtils;

import com.seeyon.ctp.common.AppContext;
import com.seeyon.v3x.services.form.FormFactory;
import com.seeyon.v3x.services.form.FormUtils;
import com.seeyon.v3x.services.form.bean.FormExport;

/**
 * 历史数据迁移 只执行一次
 * 
 * @author Administrator
 *
 */
public class OldDataManagerImpl extends BaseManager {
	private FreemarkFlowManager freemarkFlowManager;

	public FreemarkFlowManager getFreemarkFlowManager() {
		return freemarkFlowManager;
	}

	public void setFreemarkFlowManager(FreemarkFlowManager freemarkFlowManager) {
		this.freemarkFlowManager = freemarkFlowManager;
	}

	private static JdbcTemplate oldJdbcTemplate = (JdbcTemplate) AppContext.getBean("oldJdbcTemplate");

	public boolean start() {
		System.out.println("数据迁移开始");
		int num = 0;
		boolean falg = true;
		while (falg) {
			System.out.println("第几次查询：" + num);
			String sql = "select "
					+ " tblApproval.num, tblApproval.user_name as NAME, tblApproval.com_position as POST,tblApproval.org_depart as DEPT,"
					+ "tblApproval.appro_serial as FILENO, CONVERT(varchar(100), tblApproval.save_date, 120) as CREAT_DATE,"
					+ "tblApproval.subject as TITLE, tblApproval.attach, tblApproval.contents, tblApproval.attach_paper from tblApproval "
					+ "where appro_state=1 ORDER BY tblApproval.save_date DESC "
					+" offset "+1000 * num+" rows fetch next 1000 rows only";

			if (num == 1)
				System.out.println(sql);
			List<Map<String, Object>> result = oldJdbcTemplate.queryForList(sql);
			System.out.println("SQL执行完成："+sql);
			System.out.println("查询结果："+result.size());
			if (!CollectionUtils.isEmpty(result)) {
				num++;
			} else {
				falg = false;
				System.out.println("结束");
			}
			String yjSql = "select user_name as YJNAME,contents as YJ,CONVERT(varchar(100), w_date, 120) as YJDATE from tblApproval_Reply where w_num=?";
			String spSql = "select user_name as name,com_position as post,CONVERT(varchar(100), appro_date, 120) as date from tblappro_user where appro_num=?";
			String flagSql = "SELECT FIELD0015 FROM formmain_0197 WHERE FIELD0015 = ?";
			try {
				for (Map<String, Object> vm : result) {
					List<Map<String, Object>> flag = jdbcTemplate.queryForList(flagSql, vm.get("num"));
					if(!flag.isEmpty()) continue;
					List<Map<String, Object>> resultReply = oldJdbcTemplate.queryForList(yjSql, vm.get("num"));
					List<Map<String, Object>> resultUser = oldJdbcTemplate.queryForList(spSql, vm.get("num"));
					String sp = "";
					for (Map<String, Object> map : resultUser) {
						sp += map.get("name") + "|" + map.get("post") + "|" + map.get("date") + ",";
					}
					vm.put("NAMES", sp);
					vm.put("array", resultReply);
					String dataXml = this.freemarkFlowManager.fltTemplateIntoString("datasync/data/addOldData.ftl", vm);
					// 可以使用数据模板生成，这样底表事件依然有效
					FormFactory formFactory = (FormFactory) AppContext.getBean("formFactory");
					FormExport[] formExportData = FormUtils.xmlTransformFormExports(dataXml.replaceAll("[\\x00-\\x08\\x0b-\\x0c\\x0e-\\x1f]", ""));
					for (FormExport formExport : formExportData) {
						formFactory.importBusinessFormData("admin", "LOCK_OLD_0001", formExport, null);
					}
				}

			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return false;
			}

		}
		return true;
	}
	
	public Map<String, Object> getOldDataByFielNo(String fielNo){
		String getOldDataByFielNoSql = this.freemarkFlowManager.fltTemplateIntoString("datasync/data/getOldDataByFielNo.ftl", null);
		List<Map<String, Object>> resultList = jdbcTemplate.queryForList(getOldDataByFielNoSql,fielNo);
		Map<String, Object> result = resultList.get(0);
		String[] spNames=MapUtils.getString(result, "SPNAMES").split(",");
		String str = MapUtils.getString(result, "FIELNAMES");
		if(StringUtils.isNotEmpty(str)){
			String[] fielName = MapUtils.getString(result, "FIELNAMES").split("▒");
			result.put("FIELNAMES", fielName);
		}
		List<Map<String, Object>> spnameList = new ArrayList<>();
		for (int i = 0; i < spNames.length; i++) {
			Map<String, Object> map = new HashMap<>();
			String [] temp = spNames[i].split("\\|");
			map.put("spName", temp[0]);
			map.put("spPost", temp[1]);
			map.put("spData", temp[2]);
			spnameList.add(map);
		}
		result.put("SPNAMES", spnameList);
		String getOldDataYJByFormmainIDSql = this.freemarkFlowManager.fltTemplateIntoString("datasync/data/getOldDataYJByFormmainID.ftl", null);
		List<Map<String, Object>> resultYJList = jdbcTemplate.queryForList(getOldDataYJByFormmainIDSql,MapUtils.getString(result, "ID"));
		result.put("yjList", resultYJList);
		return result;
	}
}
